package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.entity.Beer;

public class BeerDAO {

	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@210.125.212.171:1521:xe";
	String userid = "beer";
	String userpasswd = "test";

	public BeerDAO() {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public int insert(String bno, String name, String nation, String type_yn,
			int price, int stock) {

		int result = 0;
		Connection con = null;
		PreparedStatement pstmt = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "insert into beer (bno, name, nation, type_yn, price, stock) values(?,?,?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, bno);
			pstmt.setString(2, name);
			pstmt.setString(3, nation);
			pstmt.setString(4, type_yn);
			pstmt.setInt(5, price);
			pstmt.setInt(6, stock);

			int n = pstmt.executeUpdate();
			result = n;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public int update(String bno, String type_yn, int price, int stock) {

		int result = 0;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "update beer set type_yn = ?, price = ?, stock = ? where bno = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, type_yn);
			pstmt.setInt(2, price);
			pstmt.setInt(3, stock);
			pstmt.setString(4, bno);

			int n = pstmt.executeUpdate();
			result = n;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public int delete(String bno) {
		int result = 0;
		Connection con = null;
		PreparedStatement pstmt = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "delete from beer where bno = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, bno);

			int n = pstmt.executeUpdate();
			result = n;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public ArrayList<Beer> select() {

		ArrayList<Beer> list = new ArrayList<Beer>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "select bno, name, nation, type_yn, price, stock from beer";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				String bno = rs.getString("bno");
				String name = rs.getString("name");
				String nation = rs.getString("nation");
				String type_yn = rs.getString("type_yn");
				int price = rs.getInt("price");
				int stock = rs.getInt("stock");

				Beer beer = new Beer(bno, name, type_yn, nation, price, stock);
				list.add(beer);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public ArrayList<Beer> selectType(boolean type) {

		ArrayList<Beer> list = new ArrayList<Beer>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "select bno, name, nation, type_yn, price, stock from beer"
					+ "where type_yn = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, (type ? "Y" : "N"));
			rs = pstmt.executeQuery();

			while (rs.next()) {
				String bno = rs.getString("bno");
				String name = rs.getString("name");
				String nation = rs.getString("nation");
				String type_yn = rs.getString("type_yn");
				int price = rs.getInt("price");
				int stock = rs.getInt("stock");

				Beer beer = new Beer(bno, name, type_yn, nation, price, stock);
				list.add(beer);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public Beer selectByID(String b_No) {

		Beer beer = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			con = DriverManager.getConnection(url, userid, userpasswd);
			String sql = "select * from beer where bNo = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, b_No);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				String bno = rs.getString("bNo");
				String name = rs.getString("name");
				String nation = rs.getString("nation");
				String type_yn = rs.getString("type_yn");
				int price = rs.getInt("price");
				int stock = rs.getInt("stock");

				beer = new Beer(bno, name, type_yn, nation, price, stock);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return beer;

	}

}
